package test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Random;
import java.util.Set;
import java.util.UUID;

import utils.DBUtils;



public class UserDao {

	private static Connection conn = DBUtils.getConnection();
	
	public static void addtable(String tableName){
		long begin = System.currentTimeMillis(); 
		try{
			 String sql = 
			 		 "CREATE TABLE "+tableName+"(id int(11) primary key,type int(11),name varchar(255),age int(11),height int(11),weight int(11));";
			 conn.createStatement().execute(sql);
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			long end = System.currentTimeMillis();  
//			System.out.println("添加表  "+tableName+"用时是：" + (end - begin) + " ms");  
		}
		
	}
	public static void addtableIndex(String tableName){
		long begin = System.currentTimeMillis(); 
		long end = 0;
		try{
			 String sql = 
			 		 "CREATE TABLE "+tableName+"(id int(11) primary key,type int(11),name varchar(255),age int(11),height int(11),weight int(11),KEY `type` (`type`) USING BTREE);";
			 conn.createStatement().execute(sql);
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			end = System.currentTimeMillis();  
		}
		long diffTime = end - begin;
		
	}
	/**
	 * @param num 添加数据库条数
	 * @param typeNum type数量
	 * @param tableName 表名字
	 */
	public static void addUser(int num,int typeNum,String tableName){
		long begin = System.currentTimeMillis(); 
		PreparedStatement ps = null;
		int id = 0;
		int z = num/typeNum;
		try {
			 conn.setAutoCommit(false);  
			 String sql = "insert into "+tableName+"(type,id,name,age,height,weight) value(?,?,?,?,?,?)";  
		     ps = conn.prepareStatement(sql);
//		     System.err.println(sql);
		     Random r = new Random();
		     //百万级以上  批量提交10w条数据
		     if(num>=1000000){
		    	 for (int i = 1; i <= num/100000; i++) {  
			         for (int k = 1; k <= 100000; k++) {  
			        	    ps.setInt(1, id%z);
			    			ps.setInt(2, id++);
			    			ps.setString(3, UUID.randomUUID().toString());													
			    			ps.setInt(4, r.nextInt(100000));		
			    			ps.setInt(5, r.nextInt(100000));
			    			ps.setInt(6, r.nextInt(100000)); 
			                ps.addBatch();  
			         }  
			         ps.executeBatch();  
			         conn.commit();
			    }  
		     }else{
		    	 for (int i = 1; i <= num/10000; i++) {  
			         for (int k = 1; k <= 10000; k++) {  
			        	    ps.setInt(1, id%z);
			    			ps.setInt(2, id++);
			    			ps.setString(3, UUID.randomUUID().toString());													
			    			ps.setInt(4, r.nextInt(100000));		
			    			ps.setInt(5, r.nextInt(100000));
			    			ps.setInt(6, r.nextInt(100000)); 
			                ps.addBatch();  
			         }  
			         ps.executeBatch();  
			         conn.commit();
			    }  
		     }
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			long end = System.currentTimeMillis();  
//		    System.out.println("向"+tableName+"表添加"+num+"条数据，用时 : " + (end - begin) + " ms");  
		}
	}
	

	/**
	 * @param deleteNum 被删除数据的末尾数
	 * @param num 添加数据库条数   
	 * @param typeNum type数量
	 * @param tableName 表名字
	 * @param testTableNum 批量提交表数量
	 */
	public static void addUserByDelete(int deleteNum,int num,int typeNum,String tableName,int testTableNum){
		long begin = System.currentTimeMillis(); 
		PreparedStatement ps = null;
		try{
			 conn.setAutoCommit(false);  
			 for(int i=0;i<testTableNum;i++){
				 String sql = "insert into "+tableName+i+"(id,type,name,age,height,weight) value(?,?,?,?,?,?)";  
			     ps = conn.prepareStatement(sql);  
			     Random r = new Random();
			     for (int j = 0; j < typeNum; j++) {  
			        	ps.setInt(1, num/typeNum*j+deleteNum);
//			        	System.err.println(num/typeNum*j+deleteNum);
			        	ps.setInt(2, deleteNum);
				    	ps.setString(3, UUID.randomUUID().toString());													
				    	ps.setInt(4, r.nextInt(100000));		
				    	ps.setInt(5, r.nextInt(100000));
				    	ps.setInt(6, r.nextInt(100000)); 
				        ps.addBatch();  
			     }  
			     ps.executeBatch();  
			     conn.commit();
			 }
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	/**
	 * @param deleteNum  被删除数据的末尾数  例如：3 2003  4003 
	 * @param total  数据库表数量 
	 * @param type   删除数量 
	 * @param tableName  表名称
	 * @param testTableNum 批量提交表数量
	 * @return
	 */
	public static long deleteByOr(int deleteNum,int total,int type,String tableName,int testTableNum){
		long diffTime = 0; // 耗时
		long end = 0;	   // 結束
		long begin = 0;	   // 开始
		PreparedStatement ps = null;
		try {
			//1
			List<String> sqlList = new ArrayList<>();
			for(int z=0;z<testTableNum;z++){
				StringBuilder sql = new StringBuilder("delete from "+tableName+z+" where ");
				for(int i=0;i<type;i++){
					sql.append(" id = ? or");
				}
				sql.delete(sql.length()-2, sql.length());
				sqlList.add(sql.toString());
			}
			conn.setAutoCommit(false);
			begin = System.currentTimeMillis(); 
			for (String sql : sqlList) {
				ps = conn.prepareStatement(sql);
				for(int j=0;j<type;j++){
					ps.setInt(j+1, total/type*j+deleteNum);
				}
				ps.addBatch();
				ps.executeBatch();
//				ps.executeUpdate();
			}
			conn.commit();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			end = System.currentTimeMillis();  
			diffTime = (end - begin)/testTableNum;
		    System.out.println(tableName+"表"+total+"条数据,根据or删除"+type+"条数据用时 : " + diffTime + " ms");  
		}
		return diffTime;
	}
	
	/**
	 * @param deleteNum  被删除数据的末尾数  例如：3 2003  4003 
	 * @param total  数据库表数量 
	 * @param type   删除数量 
	 * @param tableName  表名称
	 * @param testTableNum 批量提交表数量
	 * @return
	 */
	public static long deleteByNoIndex(int deleteNum,int total,int type,String tableName,int testTableNum){
		long diffTime = 0; // 耗时
		long end = 0;	   // 結束
		long begin = 0;	   // 开始
		PreparedStatement ps = null;
		try {
			List<String> sqlList = new ArrayList<>();
			for(int z=0;z<testTableNum;z++){
				String sql = "delete from "+tableName+z+" where type = "+deleteNum;
				sqlList.add(sql);
			}
			conn.setAutoCommit(false);
			begin = System.currentTimeMillis(); 
			for (String sql : sqlList) {
				ps = conn.prepareStatement(sql);
				ps.addBatch();
				ps.executeBatch();
//				ps.executeUpdate();
			}
			conn.commit();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		end = System.currentTimeMillis();  
		diffTime = (end - begin)/testTableNum;
		System.out.println(tableName+"表"+total+"条数据,根据no index删除"+type+"条数据用时 : " + diffTime + " ms");   
	    
		return diffTime;
	}
	
	
	public static long deleteByIndex(int deleteNum,int total,int type,String tableName,int testTableNum){
		long diffTime = 0; // 耗时
		long end = 0;	   // 結束
		long begin = 0;	   // 开始
		PreparedStatement ps = null;
		try {
			List<String> sqlList = new ArrayList<>();
			for(int z=0;z<testTableNum;z++){
				String sql = "delete from "+tableName+z+" where type = "+deleteNum;
				sqlList.add(sql);
			}
			conn.setAutoCommit(false);
			begin = System.currentTimeMillis(); 
			for (String sql : sqlList) {
				ps = conn.prepareStatement(sql);
				ps.addBatch();
				ps.executeBatch();
//				ps.executeUpdate();
			}
			conn.commit();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		end = System.currentTimeMillis();  
		diffTime = (end - begin)/testTableNum;
		 System.out.println(tableName+"表"+total+"条数据,根据index删除"+type+"条数据用时 : " + diffTime + " ms");  
	    
		return diffTime;
	}
	
	public static long deleteByIn(int deleteNum,int total,int type,String tableName,int testTableNum){
		long diffTime = 0; // 耗时
		long end = 0;	   // 結束
		long begin = 0;	   // 开始
		PreparedStatement ps = null;
		try {
			//1
			List<String> sqlList = new ArrayList<>();
			for(int z=0;z<testTableNum;z++){
				StringBuilder sql = new StringBuilder("delete from "+tableName+z+" where ");
				sql.append(" id in ( ");
				for(int i=0;i<type;i++){
					sql.append(" ?,");
				}
				sql.delete(sql.length()-1, sql.length());
				sql.append(" )");
				sqlList.add(sql.toString());
			}
			conn.setAutoCommit(false);
			begin = System.currentTimeMillis(); 
			for (String sql : sqlList) {
				ps = conn.prepareStatement(sql);
				for(int j=0;j<type;j++){
					ps.setInt(j+1, total/type*j+deleteNum);
				}
				ps.addBatch();
				ps.executeBatch();
//				ps.executeUpdate();
			}
			conn.commit();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			end = System.currentTimeMillis();  
			diffTime = (end - begin)/testTableNum;
		    System.out.println(tableName+"表"+total+"条数据,根据in删除"+type+"条数据用时 : " + diffTime + " ms");  
		}
		return diffTime;
	}
	
	public static long deleteByEveryWhere(int deleteNum,int total,int type,String tableName,int testTableNum){
		long diffTime = 0; // 耗时
		long end = 0;	   // 結束
		long begin = 0;	   // 开始
		PreparedStatement ps = null;
		try {
			//1
			List<String> sqlList = new ArrayList<>();
			for(int z=0;z<testTableNum;z++){
				StringBuilder sql = new StringBuilder("delete from "+tableName+z+" where ");
				sql.append(" id = ? ");
				sqlList.add(sql.toString());
			}
			conn.setAutoCommit(false);
			begin = System.currentTimeMillis(); 
			for (String sql : sqlList) {
				ps = conn.prepareStatement(sql);
				for(int j=0;j<type;j++){
					ps.setInt(1, total/type*j+deleteNum);
					ps.addBatch();
				}
				ps.executeBatch();
			}
			conn.commit();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			end = System.currentTimeMillis();  
			diffTime = (end - begin)/testTableNum;
		    System.out.println(tableName+"表"+total+"条数据,根据every del删除"+type+"条数据用时 : " + diffTime + " ms");  
		}
		return diffTime;
	}
	
	/**
	 * @param addNum    添加数据量
	 * @param total		原始表数据总数
	 * @param tableName  添加表名
	 * @param testTableNum 批量提交表数量
	 * @return
	 */
	public static long insertData(int addNum,int total,String tableName,int testTableNum){
		long diffTime = 0; // 耗时
		long end = 0;	   // 結束
		long begin = 0;	   // 开始
		PreparedStatement ps = null;
		Random r = new Random();
		try{
			List<String> sqlList = new ArrayList<>();
			for(int i=0;i<testTableNum;i++){
				String sql = "insert into "+tableName+i+" values(?,?,?,?,?,?)";
				sqlList.add(sql);
			}
			conn.setAutoCommit(false);
			begin = System.currentTimeMillis();
			for(String sql:sqlList){
				ps = conn.prepareStatement(sql);
				int id = total;
				for(int i=0;i<addNum;i++){
					ps.setInt(1, id++);
					ps.setInt(2, r.nextInt(id));
					ps.setString(3, UUID.randomUUID().toString());		
					ps.setInt(4, r.nextInt(100000));		
	    			ps.setInt(5, r.nextInt(100000));
	    			ps.setInt(6, r.nextInt(100000)); 
	                ps.addBatch();  
				}
				ps.executeBatch();
			}
			conn.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			end = System.currentTimeMillis();  
			diffTime = (end - begin)/testTableNum;
			System.out.println(tableName+"表"+total+"条数据,插入"+addNum+"条数据用时 : " + diffTime + " ms");  
		}
		return diffTime;
	}
	
	/**
	 * @param addNum       添加数据量
	 * @param total		       原始表数据总数
	 * @param tableName    添加表名
	 * @param testTableNum 批量提交表数量
	 * @param dataId       更新数据Id集合
	 * @return
	 */
	public static long updateData(int addNum,int total,String tableName,int testTableNum,int [] dataId){
		long diffTime = 0; // 耗时
		long end = 0;	   // 結束
		long begin = 0;	   // 开始
		PreparedStatement ps = null;
		Random r = new Random();
		try{
			List<String> sqlList = new ArrayList<>();
			for(int i=0;i<testTableNum;i++){
				String sql = "update "+tableName+i+" set type = ?,weight = ? where id = ?";
				sqlList.add(sql);
			}
			conn.setAutoCommit(false); 
			begin = System.currentTimeMillis();
			for(String sql:sqlList){
				ps = conn.prepareStatement(sql);
				for(int id:dataId){
					ps.setInt(1, r.nextInt(total));
	    			ps.setInt(2, r.nextInt(100000)); 
	    			ps.setInt(3, id);
	                ps.addBatch();  
				}
				ps.executeBatch();
			}
			conn.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			end = System.currentTimeMillis();  
			diffTime = (end - begin)/testTableNum;
			System.out.println(tableName+"表"+total+"条数据,更新"+addNum+"条数据用时 : " + diffTime + " ms");  
		}
		return diffTime;
	}
	
	/**
	 * @param delNum    删除数据量
	 * @param total		原始表数据总数
	 * @param tableName  添加表名
	 * @param testTableNum 批量提交表数量
	 * @param dataId 
	 * @return
	 */
	public static long deleteByinsert(int delNum,int total,String tableName,int testTableNum){
		 
		PreparedStatement ps = null;
		try{
			List<String> sqlList = new ArrayList<>();
			for(int i=0;i<testTableNum;i++){
				StringBuffer sql = new StringBuffer("delete from "+tableName+i+" where id in (");
				for(int j=0;j<delNum;j++){
					sql.append("?,");
				}
				sql.delete(sql.length()-1, sql.length());
				sql.append(")");
				sqlList.add(sql.toString());
			}
			conn.setAutoCommit(false);
			for(String sql:sqlList){
				int id = total;
				ps = conn.prepareStatement(sql);
				for(int i=1;i<=delNum;i++){
					ps.setInt(i, id++);
				}
				ps.addBatch();
				ps.executeBatch();
			}
			conn.commit();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return 0;
	}
	
	
	//获取随机数组
	public static Object[] random(int num){
		 Random r = new Random();
	     Set<Integer> set = new HashSet<>();
	     while(set.size() <= num){
	    	 set.add(r.nextInt(1000));
	     }
	     Object[] random = set.toArray();
		 return random;
	}
	public static void dropTable(String tableName){
		long begin = System.currentTimeMillis(); 
		PreparedStatement ps = null;
		try{
			 String sql = "DROP table if exists "+tableName;
			 ps = conn.prepareStatement(sql);
			 ps.execute();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			long end = System.currentTimeMillis();  
//		    System.out.println("删除表  "+tableName+"用时是：" + (end - begin) + " ms");  
		}
		
	}
	/**
	 * @param selectNum 查询数量
	 * @param total	    表数据总数
	 * @param tableName 表名
	 * @param testTableNum 批量操作表数量
	 * @param dataId   查询数据Id集合
	 */
	public static long selectData(int selectNum,int total,String tableName,int testTableNum,int[] dataId){
		long begin = 0;
		long end = 0;
		long diffTime = 0;
		PreparedStatement ps = null;
		try{
			conn.setAutoCommit(false);
			List<String> sqlList = new ArrayList<>();
			for(int i=0;i<testTableNum;i++){
				StringBuilder sql = new StringBuilder("select * from "+tableName+i+" where type in ( ");
				for(int j=0;j<selectNum;j++){
					sql.append(" ?,");
				}
				sql.delete(sql.length()-1, sql.length());
				sql.append(" )");
				sqlList.add(sql.toString());
				
			}
			begin = System.currentTimeMillis();
			for(String sql:sqlList){
				ps = conn.prepareStatement(sql);
				int i = 1;
				for(int id:dataId){
					ps.setInt(i++, id);
				}
				ps.addBatch();
				ps.executeQuery();
			}
			conn.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			end = System.currentTimeMillis();
			diffTime = (end - begin)/testTableNum;
			System.out.println(tableName+"表"+total+"条数据,查询"+selectNum+"条数据用时 : " + diffTime + " ms");  
		}
		return diffTime;
	}
}
